﻿using System;
using System.Collections.Generic;
using System.Data;
using cashbook_psql_cs.util;
using DocumentFormat.OpenXml.Bibliography;
using Login.DBHelper;
using Npgsql;
using OfficeOpenXml;


namespace cashbook_psql_cs.DAL
{
    internal class RelationBillDAL
    {
        public DataTable GetRelationBill() {
            SQLHelper sqlHelper = new SQLHelper();
            string sql = "select id,sztype,amount,cate,account,sztime,relperson,remark from relation_bill order by ID DESC";
            return sqlHelper.ExecuteDataTable(sql);
        }

        internal void DeleteRelationBill(RelationBill rb)
        {
            SQLHelper sqlHelper = new SQLHelper();
            string sql = "delete from relation_bill where id =@id";
            NpgsqlParameter[] parms ={
                                      new NpgsqlParameter("@id",rb.Id)
                                  };

            sqlHelper.ExecuteNonQuery(sql, parms);
        }

        internal NpgsqlDataReader FindExpenseByMonth(string sql)
        {
            SQLHelper sqlHelper = new SQLHelper();
            //请创建一个SqlDataReader  读取器类型
            NpgsqlDataReader sdr = sqlHelper.ExecuteSqlReader(sql);
            return sdr;
        }

        internal NpgsqlDataReader FindIncomeByMonth(string sql)
        {
            SQLHelper sqlHelper = new SQLHelper();
            //请创建一个SqlDataReader  读取器类型
            NpgsqlDataReader sdr = sqlHelper.ExecuteSqlReader(sql);
            return sdr;
        }

        internal void SaveRelationBill(RelationBill rb)
        {
            SQLHelper sqlHelper = new SQLHelper();
            string sql = "INSERT INTO relation_bill (sztype,amount,cate,account,sztime,relperson,remark)  VALUES(@sztype, @amount,@cate,@account,@sztime,@relperson,@remark)";
            NpgsqlParameter[] parms ={
                                      new NpgsqlParameter("@sztype",rb.SZType),
                                      new NpgsqlParameter("@amount",rb.Amount),
                                      new NpgsqlParameter("@cate",rb.Cate),
                                      new NpgsqlParameter("@account",rb.Account),
                                      new NpgsqlParameter("@sztime",rb.SZTime),
                                      new NpgsqlParameter("@relperson",rb.RelPerson),
                                      new NpgsqlParameter("@remark",rb.Remark),
                                  };

            sqlHelper.ExecuteNonQuery(sql, parms);
        }

        internal void UpdateRelationBill(RelationBill rb)
        {
            SQLHelper sqlHelper = new SQLHelper();
            string sql = "update relation_bill set sztype =@sztype,amount =@amount,cate=@cate,account=@account,sztime=@sztime,relperson=@relperson,remark=@remark  where id =@id";
            NpgsqlParameter[] parms ={
                                      new NpgsqlParameter("@id",rb.Id),
                                      new NpgsqlParameter("@sztype",rb.SZType),
                                      new NpgsqlParameter("@amount",rb.Amount),
                                      new NpgsqlParameter("@cate",rb.Cate),
                                      new NpgsqlParameter("@account",rb.Account),
                                      new NpgsqlParameter("@sztime",rb.SZTime),
                                      new NpgsqlParameter("@relperson",rb.RelPerson),
                                      new NpgsqlParameter("@remark",rb.Remark),
                                  };

            sqlHelper.ExecuteNonQuery(sql, parms);
        }

        internal List<RelationBill> FindDetailsByMonth(int year, int month, string type)
        {
            List<RelationBill> result = new List<RelationBill>();
            SQLHelper sqlHelper = new SQLHelper();
            try
            {
                DateTime startDate = new DateTime(year, month, 1);
                DateTime endDate = startDate.AddMonths(1).AddDays(-1);

                string sql = @"SELECT * FROM relation_bill 
                      WHERE sztime >= @startDate 
                      AND sztime <= @endDate 
                      AND sztype = @type
                      ORDER BY sztime";

                NpgsqlParameter[] parms = {
                    new NpgsqlParameter("@startDate", startDate),
                    new NpgsqlParameter("@endDate", endDate),
                    new NpgsqlParameter("@type", type)
                };

                // Execute query and get data reader
                using (var reader = sqlHelper.ExecuteSqlReader(sql, parms))
                {
                    while (reader.Read())
                    {
                        result.Add(new RelationBill
                        {
                            // Map your columns to object properties
                            Id = reader.GetInt32(reader.GetOrdinal("id")),
                            SZType = reader.GetString(reader.GetOrdinal("sztype")),
                            Amount = reader.GetDecimal(reader.GetOrdinal("amount")),
                            Cate = reader.GetString(reader.GetOrdinal("cate")),
                            Account = reader.GetString(reader.GetOrdinal("account")),
                            SZTime = reader.GetDateTime(reader.GetOrdinal("sztime")),
                            RelPerson = reader.GetString(reader.GetOrdinal("relperson")),
                            Remark = reader.GetString(reader.GetOrdinal("remark"))

                            // Add other properties as needed
                        });
                    }
                }
            }finally
            {
                // 手动释放资源
                if (sqlHelper is IDisposable disposable)
                {
                    disposable.Dispose();
                }
            }
            return result;
        }

        internal NpgsqlDataReader FindMonthlyData(string sqlTemplate, int year, string type)
        {
            var parameters = new NpgsqlParameter[] {
                new NpgsqlParameter("@year", year),
                new NpgsqlParameter("@type", type)
            };
            SQLHelper sqlHelper = new SQLHelper();
            return sqlHelper.ExecuteSqlReader(sqlTemplate, parameters);
        }

        internal void ReadExcelDataToDb(string filePath)
        {
            // 从Excel读取数据
            List<RelationBill> relationBills = ImportHelper.ReadExcelWithEPPlus<RelationBill>(filePath);
            // 批量插入PostgreSQL
            SQLHelper sqlHelper = new SQLHelper();
            sqlHelper.BulkInsert(relationBills);
        }
    }
}
